package dbv;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Locale;

import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

public class  renatre
{
	public static void fileHeader(WritableSheet s) throws WriteException, SQLException{
		WritableFont wf = new WritableFont(WritableFont.ARIAL,10, WritableFont.BOLD);
		  WritableCellFormat cf = new WritableCellFormat(wf);
		  cf.setWrap(false);

		  Label l;
		  l = new Label(0,0,"fecha_de_ingreso");
		  s.addCell(l);
		  l = new Label(1,0,"tarea_realizada");
		  s.addCell(l);
		  l = new Label(2,0,"trab");
		  s.addCell(l);
		  l = new Label(3,0,"periodo1");
		  s.addCell(l);
		  l = new Label(4,0,"dias_periodo1");
		  s.addCell(l);
		  l = new Label(5,0,"rem_suj_aportes_1");
		  s.addCell(l);
		  l = new Label(6,0,"periodo2");
		  s.addCell(l);
		  l = new Label(7,0,"dias_periodo2");
		  s.addCell(l);
		  l = new Label(8,0,"rem_suj_aportes_2");
		  s.addCell(l);
		  l = new Label(9,0,"periodo3");
		  s.addCell(l);
		  l = new Label(10,0,"dias_periodo3");
		  s.addCell(l);
		  l = new Label(11,0,"rem_suj_aportes_3");
		  s.addCell(l);
		  l = new Label(12,0,"conv_corresp");
		  s.addCell(l);
		  l = new Label(13,0,"fecha_de_cese");
		  s.addCell(l);
		  l = new Label(14,0,"cuil");
		  s.addCell(l);
		  l = new Label(15,0,"cuit");
		  s.addCell(l);
		  l = new Label(16,0,"razon_social");
		  s.addCell(l);
		  l = new Label(17,0,"check");
		  s.addCell(l);
		  l = new Label(18,0,"formulario");
		  s.addCell(l);
		  l = new Label(19,0,"Fecha_formulario");
		  s.addCell(l);
		  l = new Label(20,0,"Delegacion");
		  s.addCell(l);
		  l = new Label(21,0,"control");
		  s.addCell(l);
		  l = new Label(22,0,"tipo_ingreso");
		  s.addCell(l);
		  l = new Label(23,0,"legajo");
		  s.addCell(l);
		  l = new Label(24,0,"apyn_trabajador");
		  s.addCell(l);
		  l = new Label(25,0,"t_formulario");
		  s.addCell(l);
		  l = new Label(26,0,"lugar");
		  s.addCell(l);
		  l = new Label(27,0,"renatre");
		  s.addCell(l);
		  l = new Label(28,0,"code");
		  s.addCell(l);
		  l = new Label(29,0,"frmlrenatre");
		  s.addCell(l);
		  l = new Label(30,0,"localidad");
		  s.addCell(l);
		  l = new Label(31,0,"provincia");
		  s.addCell(l);
		  l = new Label(32,0,"flr1");
		  s.addCell(l);
		  l = new Label(33,0,"flr2");
		  s.addCell(l);

	}
	
	
	public static ResultSet renatreConsulta(int legajo, String periodo, String base){
		try {
			Connection conexion = DriverManager.getConnection("jdbc:mysql://localhost:3306/"+base, "", ""); 
			String query="CALL aportes_Trimestrales(?,?)";
			PreparedStatement ps = conexion.prepareStatement(query);
			ps.setInt(1, legajo);
			ps.setString(2, periodo);
			ps.execute();
			ps.clearParameters();
			String sql = "select legajo, DATE_FORMAT(periodo1,'%m/%Y') AS fecha1 ,diasT1,aportes1," +
					"DATE_FORMAT(periodo2,'%m/%Y') AS fecha2 ,diasT2, aportes2, DATE_FORMAT(periodo3,'%m/%Y') AS fecha3 " +
					",diasT3,aportes3,DATE_FORMAT(ingreso,'%d/%m/%Y'),cuil,tarea,nombre,ano,convenio from aportes";
			ps = conexion.prepareStatement(sql);	
			ps.execute();
			return ps.getResultSet();
		}catch(Exception e){
			e.printStackTrace();
		}
		return null;
	}

	private static void writeDataSheet(WritableSheet s, int empresa, String fecha, String base) throws WriteException, SQLException{
		WritableFont wf = new WritableFont(WritableFont.ARIAL,10, WritableFont.BOLD);
		WritableCellFormat cf = new WritableCellFormat(wf);
		cf.setWrap(false);

		Empresa emp = new Empresa();
		emp.getDataDB(empresa);
		
		
		
		Label l;


		int i=1;

		
		//{150,160,162,172,180,191} CLAVIER BM
		
		//{64,71,74,77} aztiria
		
		//{1,48,68,222,223,237,248,253,255,256,257,263,264}; CLAVIER LINCOLN
   
		int[] j = {99};
		
		for (int k = 0; k < j.length; k++) {
			
			
		ResultSet rs = renatreConsulta(j[k],fecha, base);
		
		rs.beforeFirst();
		while ( rs.next()) {
			l = new Label(0,i,rs.getString(11)); //fecha de ingreso
			s.addCell(l);
			
		   l = new Label(1,i,rs.getString(13)); //tarea
		   s.addCell(l);
		/*   if (rs.getString(14).equalsIgnoreCase("J")){
			   l = new Label(2,i,"NO PERMANENTE"); //MODALIDAD DE CONTRATACION
			   s.addCell(l);
		   }else {
			   l = new Label(2,i,"PERMANENTE"); //MODALIDAD DE CONTRATACION
			   s.addCell(l);
		   }*/
			 
			//periodo 1 
			l = new Label(3,i,rs.getString(2)); //periodo 1
			s.addCell(l);
			l = new Label(4,i,rs.getString(3)); // dias trabajados 1
			s.addCell(l);
			l = new Label(5,i,rs.getString(4)); // importe bruto 1
			s.addCell(l);
			//Periodo 2 
			l = new Label(6,i,rs.getString(5)); //periodo 2
			s.addCell(l);
			l = new Label(7,i,rs.getString(6)); //dias trabajados 2
			s.addCell(l);
			l = new Label(8,i,rs.getString(7)); // importe bruto 2
			s.addCell(l);

			//Periodo 3
			l = new Label(9,i,rs.getString(8));  //periodo 3
			s.addCell(l);
			l = new Label(10,i,rs.getString(9)); //dias trabajados 3
			s.addCell(l);
			l = new Label(11,i,rs.getString(10)); // importe bruto 3
			s.addCell(l);

			//Convenio
			l = new Label(12,i,"NO");
			s.addCell(l);

			//CUIL
			l = new Label(14,i,rs.getString(12));
			s.addCell(l);

			//CUIT
			l = new Label(15,i, emp.getCuit());
			s.addCell(l);
			
			//Nombre
			l = new Label(16,i, emp.getNombre());
			s.addCell(l);
			
			

			//Legajo y Nombre
			l = new Label(23,i,rs.getString(1));
			s.addCell(l);
			l = new Label(24,i,rs.getString(14));
			s.addCell(l);


			
			l = new Label(30,i,emp.getLocalidad());
			s.addCell(l);
			l = new Label(31,i,emp.getProvincia());
			s.addCell(l);
			
			
			
			
			
			i++;
		}// end while
		
		}// end for
		
	}



	public static void main(String[] archivo) throws SQLException {
		try
		{
			int e = 13;
			String fecha = "2010-10-01";
			String base = "huaicos";
			String filename = "c:/Renatre - Cupones - "+base+".xls";
			WorkbookSettings ws = new WorkbookSettings();
			ws.setLocale(new Locale("ar", "ES"));
			WritableWorkbook workbook = 
				Workbook.createWorkbook(new File(filename), ws);
			WritableSheet s = workbook.createSheet("Hoja1", 0);
			fileHeader(s);
			writeDataSheet(s,e,fecha, base);
			workbook.write();
			workbook.close();      
		}
		catch (IOException e)
		{
			e.printStackTrace();
		}
		catch (WriteException e)
		{
			e.printStackTrace();
		}
	}



}



